const sql = require('mssql')

/**
 * 查询商品信息接口
 */
const getProdutInfo = (body = {}) => {

    let startSql = `SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY BM DESC) AS PX,TM,BM,MC,GG,JP,SCCJ,CDMC,PZWH,MJSL,CCTJ,JXMC FROM BM_YP WHERE BM <>ALL (SELECT YPBM FROM YPZL)`;

    const endSql = `) AS JZJG WHERE JZJG.PX BETWEEN ${body.size * body.index - 9} AND ${body.size * body.index}`

    if (body.bm) {
        startSql += ` AND BM LIKE '%${body.bm}%'`
    }

    if (body.tm) {
        startSql += ` AND TM LIKE '%${body.tm}%'`
    }

    if (body.mc) {
        startSql += ` AND MC LIKE '%${body.mc}%'`
    }

    if (body.jp) {
        startSql += ` AND JP LIKE '%${body.jp}%'`
    }

    startSql += `${endSql}`

    return sql.query(startSql)

}

/**
 * 获取列表数据总数量
 */
const getProductTotal = () => {
    return sql.query`SELECT COUNT(BM) AS TOTAL FROM BM_YP`
}

/**
 * 新增商品资料数据接口
 */
const getAddData = (body) => {
    let startSql = `INSERT INTO YPZL (YPBM,JXMC,NFWY,CCTJ,SPLX)`
    let endSql = ``

    body.forEach((e, i) => {
        if (body.length === (i + 1)) {
            endSql += ` SELECT '${e.BM}', '${e.jxmc}', '${e.nfwy}', '${e.cctj}' ,'${e.splx}'`
        } else {
            endSql += ` SELECT '${e.BM}', '${e.jxmc}', '${e.nfwy}', '${e.cctj}' ,'${e.splx}' UNION`
        }
    })
    startSql += endSql

    return sql.query(startSql)
}

/**
 * 查询商品资料总条数接口
 */
const getTotal = (body) => {
    let selectSql = ` SELECT COUNT(Y.YPBM) AS TOTAL FROM YPZL Y LEFT JOIN BM_YP B ON Y.YPBM = B.BM `;
    if (body.ypbm) {
        selectSql += ` WHERE (B.BM LIKE '%${body.ypbm}%' OR B.MC LIKE '%${body.ypbm}%' OR B.JP LIKE '%${body.ypbm}%') `;
    }
    return sql.query(selectSql)
}

/**
 * 查询商品资料接口
 */
const getProduct = (body) => {
    let startSql = `SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY Y.YPBM DESC) AS PX,Y.YPBM,Y.JXMC,Y.NFWY,Y.CCTJ,Y.SPLX,B.MC FROM YPZL Y LEFT JOIN BM_YP B ON Y.YPBM = B.BM `;

    if (body.ypbm) {
        startSql += ` WHERE (B.BM LIKE '%${body.ypbm}%' OR B.MC LIKE '%${body.ypbm}%' OR B.JP LIKE '%${body.ypbm}%') `;
    }
    let endSql = `) AS JZJG WHERE JZJG.PX BETWEEN ${body.size * body.index - 9} AND ${body.size * body.index}`;
    startSql += endSql;
    return sql.query(startSql)
}

/**
 * 更新商品资料接口
 */
const updateProduct = body => {
    return sql.query`UPDATE YPZL SET JXMC= ${body.JXMC},NFWY= ${body.NFWY},CCTJ = ${body.CCTJ},SPLX = ${body.SPLX} WHERE YPBM = ${body.YPBM}`
}


/**
 * 商家编码用Excel批量导入接口
 */
const insertExcel = async body => {
    let startSql = `INSERT INTO BMPP (YPBM,STBM)`
    let endSql = ``

    body.forEach(async (e, i) => {
        if (body.length === (i + 1)) {
            endSql += ` SELECT '${e.YPBM}','${e.STBM}' WHERE NOT EXISTS (SELECT * FROM BMPP WHERE YPBM = '${e.YPBM}' AND STBM = '${e.STBM}')`
        } else {
            endSql += ` SELECT '${e.YPBM}','${e.STBM}' WHERE NOT EXISTS (SELECT * FROM BMPP WHERE YPBM = '${e.YPBM}' AND STBM = '${e.STBM}') UNION`
        }
    })
    startSql += endSql

    return sql.query(startSql)
}

/**
 * 查询商家编码信息接口
 */
const getCoding = body => {
    return sql.query`SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY YPBM DESC) AS PX, * FROM BMPP) AS JZJG WHERE JZJG.PX BETWEEN ${body.size * body.index - 9} AND ${body.size * body.index}`
}

/**
 * 查询商家编码总条数接口
 */
const getCodingTotal = () => {
    return sql.query`SELECT COUNT(YPBM) AS TOTAL FROM BMPP`
}


/**
 * 查询货位架信息接口
 */
const getCarriageRack = body => {
    return sql.query`SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY J.HWBH DESC) AS PX,* FROM (SELECT DISTINCT  H.HWBH,H.HWLX,T.YPBM,SUM(T.KCSL) OVER(PARTITION BY YPBM) AS SL FROM HWBH H INNER JOIN KC_KCDTB AS T ON T.HWBH=H.HWBH WHERE T.HWBH IS NOT NULL AND T.HWBH <> '') AS J) AS K WHERE K.PX BETWEEN ${body.size * body.index - 9} AND ${body.size * body.index}`
}


module.exports = {
    getTotal,
    getProduct,
    getProdutInfo,
    getProductTotal,
    getAddData,
    getCoding,
    getCodingTotal,
    getCarriageRack,
    updateProduct,
    insertExcel
}
